﻿use dbcrm;

if exists (select null from sys.tables where name = 'person_events')
	drop table person_events;
if exists (select null from sys.tables where name = 'persones')
	drop table persones;
if exists (select null from sys.tables where name = 'company_events')
	drop table company_events;
if exists (select null from sys.tables where name = 'companies')
	drop table companies;
if exists (select null from sys.tables where name = 'logins')
	drop table logins;
if exists (select null from sys.tables where name = 'user_events')
	drop table user_events;
if exists (select null from sys.tables where name = 'event_files')
	drop table event_files;
if exists (select null from sys.tables where name = 'events')
	drop table events;
if exists (select null from sys.tables where name = 'refs')
	drop table refs;
if exists (select null from sys.tables where name = 'users')
	drop table users;
if exists (select null from sys.tables where name = 'roles')
	drop table roles;
if exists (select null from sys.tables where name = 'time_zones')
	drop table time_zones;
go


-- TODO: add change summer time rules
create table time_zones(
	name varchar(100) not null primary key,
	s_hour int not null,
	s_min int not null default(0),
	w_hour int not null,
	w_min int not null default(0)
);

create table roles(
	id int not null identity primary key,
	name varchar(100) not null
);

create table users(
	id int not null identity primary key,
	dt datetime not null default(getutcdate()),
	boss int not null references users(id),
	time_zone varchar(100) not null references time_zones(name),
	f_name nvarchar(100) not null,
	l_name nvarchar(100) not null,
	m_name nvarchar(100) null
);

create table ref_schemes(
	id int not null identity primary key,
	code varchar(100) not null,  -- examples: 'http' or 'tel'
	suffix varchar(100) not null -- examples: '//' or ''
);

create table refs(
	id int not null identity primary key,
	scheme_id int not null references ref_schemes(id),
	dt datetime not null default(getutcdate()),
	ref nvarchar(1000) not null
);

create table events(
	id int not null identity primary key,
	dt datetime not null default(getutcdate()),
	dt_upd datetime not null default(getutcdate()),
	dt_beg datetime not null,
	dt_end datetime,
	categ nvarchar(100) not null, -- признак группировки в отдельный календарь 
	summary nvarchar(100) not null,
	note nvarchar(max),
	recurrence varchar(500),
	notifications varchar(500),
	resource_id int -- ссылка на сущность
);

create table event_refs(
	event_id int not null references events(id),
	ref_id int not null references refs(id)
);

create table user_events(
	user_id int not null references users(id),
	event_id int not null references events(id),
	constraint pk_company_events primary key (user_id, event_id)
);

create table logins(
	id int not null identity primary key,
	user_id int not null references users(id),
	dt_beg datetime not null default(getutcdate()),
	dt_end datetime,
	login nvarchar(100) not null,
	password nvarchar(100) not null
);

create table companies(
	id int not null identity primary key,
	dt datetime not null default(getutcdate()),
	title nvarchar(200) not null,
	note nvarchar(max)
);

create table company_events(
	company_id int not null references companies(id),
	event_id int not null references events(id)
);

create table persones(
	id int not null identity primary key,
	dt datetime not null default(getutcdate()),
	title nvarchar(200) not null,
	company_id int references companies(id)
);

create table person_events(
	user_id int not null references users(id),
	event_id int not null references events(id)
);

